package com.bbcare.followup.plat.service.impl;

import java.util.*;
import java.util.stream.Collectors;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import com.bbcare.followup.plat.service.IKailiService;


/**
 * 凯里体检业务。
 * @author Michael Zhu.
 */
@Service
public class KailiService implements IKailiService {
    private final static Logger logger = Logger.getLogger(KailiService.class);
    //查询儿童体检的一级指标。
    private final static String QUERY_CHILD_INDEX1  = "SELECT DISTINCT(index_id) AS index_id, index_name FROM kaili_child_index";
    //查询儿童体检的二级指标。
    private final static String QUERY_CHILD_INDEX2  = "SELECT index_value FROM kaili_child_index WHERE index_id='%INDEXID%'";

    //生产环境的tenant_id:klsfybjyzfta001，   queue_id:klsfybjyzfjcbjqueue.
    //测试环境的tenant_id:klsfybjy01zfta001， queue_id:klsfybjy01zfjcbjqueue.
    private final static String QUERY_BABY_LIST1    =
            "SELECT g.*, h.id AS task_id, h.pt_schemeid, h.finish_date, h.update_time, h.queue_id FROM (SELECT e.*,f.question_answer AS 'class' FROM (SELECT c.*, d.question_answer AS 'school' " +
            "FROM (SELECT a.id AS user_id, a.user_name, a.gender, a.birthday, a.phone_no FROM pt_user_info a WHERE a.tenant_id='klsfybjyzfta001' AND a.id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1')) AS c LEFT JOIN pt_contentdata_info d ON c.user_id=d.user_id WHERE d.question_id='jd_yeymc' " +
            ") AS e LEFT JOIN pt_contentdata_info f ON e.user_id=f.user_id WHERE f.question_id='jd_banji') AS g LEFT JOIN pt_task_info h ON h.user_id=g.user_id " +
            "WHERE h.queue_id='klsfybjyzfjcbjqueue' AND h.seq_no!=0";
    private final static Integer PAGE_SIZE    = 10;
    private final static String QUERY_BABY_LIST2_1  =
            "SELECT g.*, h.question_answer AS 'class' FROM (SELECT e.*, f.question_answer AS 'school' FROM (SELECT c.*, d.id AS task_id, d.queue_id, d.pt_schemeid, d.finish_date, d.update_time FROM (SELECT a.user_id, b.user_name, b.gender, b.birthday, b.phone_no "+
            "FROM pt_contentdata_info a LEFT JOIN pt_user_info b ON a.user_id=b.id WHERE a.question_id='%INDEX1%' AND a.question_answer LIKE '%%INDEX2%%' AND b.tenant_id='klsfybjyzfta001' AND b.id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1')) AS c "+
            "LEFT JOIN pt_task_info d ON d.user_id=c.user_id WHERE d.queue_id='klsfybjyzfjcbjqueue' AND d.seq_no!=0 AND d.finish_date IS NOT NULL ";
    private final static String QUERY_BABY_LIST2_2  =
            ") AS e LEFT JOIN pt_contentdata_info f ON f.user_id=e.user_id WHERE f.question_id='jd_yeymc') AS g LEFT JOIN pt_contentdata_info h ON h.user_id=g.user_id WHERE h.question_id='jd_banji' ORDER BY g.update_time DESC ";

    //查询幼儿园列表。
    private final static String QUERY_NURSERY_LIST  = "SELECT * FROM kaili_nursery WHERE delete_flag='N' ORDER BY sortno ASC";


    @Autowired
    private JdbcTemplate jdbc;
    @Autowired
    private NamedParameterJdbcTemplate namedJdbc;

    /**
     * 模糊查询幼儿园列表。
     * @param nameLike
     * @return
     */
    @Override
    public Map<String,Object> queryNurseryList(final String nameLike) {
        String querySql = "SELECT name FROM kaili_nursery WHERE delete_flag='N' ";
        if (StringUtils.isNotBlank(nameLike))   {
            querySql += "AND name LIKE '%" + nameLike + "%'";
        }
        logger.info("queryNurseryList() sql:"+querySql);
        final List<Map<String, Object>> resultList = jdbc.queryForList(querySql);
        Map<String,Object> retMap   = new HashMap<>();
        retMap.put("total", resultList.size());
        retMap.put("rows",  resultList);
        return retMap;
    }

    /**
     * 增加1个新的幼儿园。
     * @param paraMap
     * @return
     */
    @Override
    public int insertNursery(final Map<String,Object> paraMap) {
        String maxidSql = "SELECT MAX(id) FROM kaili_nursery";
        Integer maxid   = jdbc.queryForObject(maxidSql, Integer.class);
        String insertSql= "INSERT INTO kaili_nursery (id,name) VALUES (:id, :name)";
        Map<String,Object> sqlParaMap   = new HashMap<>();
        String name     = paraMap.get("name").toString();
/*
        String street   = paraMap.get("street").toString();
        String address  = paraMap.get("address").toString();
        String xingzhi  = paraMap.get("xingzhi").toString();
*/
        Integer myid    = maxid+1;
        sqlParaMap.put("id", myid);
        sqlParaMap.put("name", name);
/*
        sqlParaMap.put("street", street);
        sqlParaMap.put("address", address);
        sqlParaMap.put("xingzhi", xingzhi);
*/
        int insRet = 0;
        try {
            insRet = this.namedJdbc.update(insertSql, sqlParaMap);
        } catch (DataAccessException dae)   {
            logger.error(dae.getMessage());
        }
        return insRet;
    }

    private Map<String,String> getSqlWithoutIndex(final String startDate, final String endDate, final int start, final int pageSize)    {
        String sql  = QUERY_BABY_LIST1;
        if (StringUtils.isNotBlank(startDate))  {
            sql += " AND h.finish_date>='" + startDate + "'";
        }
        if (StringUtils.isNotBlank(endDate))    {
            sql += " AND h.finish_date<='" + endDate +"'";
        }
        final String countSql = "SELECT COUNT(*) FROM (" + sql + ") AS cntable";
//        sql += " ORDER BY h.finish_date DESC LIMIT " + start + ", " + pageSize;
        sql += " ORDER BY h.update_time DESC LIMIT " + start + ", " + pageSize;
        Map<String,String> retMap   = new HashMap<>();
        retMap.put("querySql", sql);
        retMap.put("countSql", countSql);
        return retMap;
    }
    private Map<String,String> getSqlWithIndex(final String startDate, final String endDate, final String index1, final String index2, final int start, final int pageSize) {
        String sql  = QUERY_BABY_LIST2_1.replaceAll("%INDEX1%", index1);
        sql = sql.replaceAll("%INDEX2%", index2);
        if (StringUtils.isNotBlank(startDate))  {
            sql += "AND d.finish_date>='" + startDate +"' ";
        }
        if (StringUtils.isNotBlank(endDate))  {
            sql += "AND d.finish_date<='" + endDate +"' ";
        }
        sql += QUERY_BABY_LIST2_2;
        final String countSql = "SELECT COUNT(*) FROM (" + sql + ") AS cntable";
        sql += " LIMIT " + start + ", " + pageSize;
        Map<String,String> retMap   = new HashMap<>();
        retMap.put("querySql", sql);
        retMap.put("countSql", countSql);
        return retMap;
    }

    /**
     * 眼视力相关。
     */
    private Map<String,String> getSpecialIndex(final String indexName)  {
        Map<String,String> retMap   = new HashMap<>();
        if ("左眼外观正常".equals(indexName)) {
            retMap.put("sf_zywg", "正常");
        } else if ("左眼外观异常".equals(indexName)) {
            retMap.put("sf_zywg", "异常");
        } else if ("右眼外观正常".equals(indexName)) {
            retMap.put("sf_yywg", "正常");
        } else if ("右眼外观异常".equals(indexName)) {
            retMap.put("sf_yywg", "异常");
        } else if ("左眼视力正常".equals(indexName)) {
            retMap.put("sf_slpj", "左眼视力正常");
        } else if ("左眼视力不良".equals(indexName)) {
            retMap.put("sf_slpj", "左眼视力不良");
        } else if ("右眼视力正常".equals(indexName)) {
            retMap.put("sf_slpj", "右眼视力正常");
        } else if ("右眼视力不良".equals(indexName)) {
            retMap.put("sf_slpj", "右眼视力不良");
        } else if ("左眼视力不配合".equals(indexName)) {
            retMap.put("sf_zysl", "不配合");
        } else if ("右眼视力不配合".equals(indexName)) {
            retMap.put("sf_yysl", "不配合");
        } else {
            retMap.put("sf_wfcl", "无法处理");
        }

        return retMap;
    }

    /**
     * 查询已完成体检的儿童列表。
     * @param paraMap
     */
    public Map<String,Object> queryBabyList(final Map<String,Object> paraMap) {
        final String startDate  = (paraMap.get("startDate")==null)?"":paraMap.get("startDate").toString();
        final String endDate    = (paraMap.get("endDate")  ==null)?"":paraMap.get("endDate").toString();
        String index1 = (paraMap.get("index1")==null)?"":paraMap.get("index1").toString();
        String index2 = (paraMap.get("index2")==null)?"":paraMap.get("index2").toString();
        Integer pageNo = (Integer)paraMap.get("pageNo");
        if (null == pageNo || pageNo < 1) {
            //缺省是第1页。
            pageNo  = 1;
        }
        Integer pageSize    = (Integer) paraMap.get("pageSize");
        if (null == pageSize || pageSize < 1)   {
            pageSize    = PAGE_SIZE;
        }
        final int start = (pageNo-1)*pageSize;

        Map<String,String> sqlMap;
        if (StringUtils.isBlank(index1))    {
            sqlMap  = getSqlWithoutIndex(startDate,endDate, start, pageSize);
        } else {
            if (index1.equals("sf_yslxg"))    {
                //眼视力相关需特殊处理。
                Map<String,String> map = getSpecialIndex(index2);
                index1  = map.keySet().stream().collect(Collectors.toList()).get(0);
                index2  = map.get(index1);
            }
            if (index1.equals("sf_xfpj"))    {
                //心脏相关需特殊处理。
                if (-1 != index2.indexOf("正常")) {
                    index2  = "双侧胸廓对称无畸形";
                }
            }
            if (index1.equals("sf_kqpj"))    {
                //龋齿相关需特殊处理。
                if (index2.equals("是")) {
                    index2  = "龋齿";
                } else {
                    index2  = "正常";
                }
            }
            logger.info("queryBabyList() index1:"+index1+", index2:"+index2);
            sqlMap  = getSqlWithIndex(startDate,endDate, index1, index2, start, pageSize);
        }
        final String querySql= sqlMap.get("querySql");
        final String countSql= sqlMap.get("countSql");
        logger.info("++++queryBabyList() querySql:"+querySql);
        logger.info("++++queryBabyList() countSql:"+countSql);

        final List<Map<String,Object>> resultList   = jdbc.queryForList(querySql);
        logger.info("++++queryBabyList() resultList size:"+resultList.size());
        final Integer total                         = jdbc.queryForObject(countSql, Integer.class);
        Map<String,Object> retMap   = new HashMap<>();
        retMap.put("total",     total);
        retMap.put("resultList",resultList);
        return retMap;
    }



    /**
     * 查询儿童体检的一级指标。
     * @return
     */
    public List<Map<String,Object>> queryChildIndex1()   {
        List<Map<String,Object>> resultList = jdbc.queryForList(QUERY_CHILD_INDEX1);

        return resultList;
    }

    /**
     * 查询儿童体检的二级指标。
     * @param index1
     * @return
     */
    public List<Map<String,String>> queryChildIndex2(final String index1)    {
        String sql  = QUERY_CHILD_INDEX2.replaceAll("%INDEXID%", index1);
        List<String> resultList = jdbc.queryForList(sql, java.lang.String.class);
        List<Map<String,String>> retList    = new ArrayList<>();
        for(String name : resultList)   {
            Map<String,String> rowMap   = new HashMap<>();
            rowMap.put("name", name);
            retList.add(rowMap);
        }

        return retList;
    }

/*统计大屏-----------------------------------------------------------------------------*/
    //全部患者数量。
    private final static String CENTER_PATIENT_SQL  = "SELECT COUNT(*) FROM pt_queuescheme_info WHERE user_status='1'";
    //儿童体检数量。
    private final static String CENTER_PATIENT_SQL1 = "SELECT COUNT(*) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='klsfybjyzfjcbjqueue'";
    //成人体检数量。
    private final static String CENTER_PATIENT_SQL2 = "SELECT COUNT(*) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='klsfybjyzfcrtjqueue'";

    //男宝人数。
//    private final static String BOTTOM_GENDER_SQL1  = "SELECT COUNT(*) FROM pt_user_info a WHERE a.tenant_id='klsfybjy01zfta001' AND a.user_name NOT LIKE '测%' AND a.id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1') AND a.gender='N'";
    private final static String BOTTOM_GENDER_SQL1  = "SELECT COUNT(*) FROM pt_user_info a WHERE a.tenant_id='klsfybjyzfta001' AND a.user_name NOT LIKE '测%' AND a.id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1') AND a.gender='N'";
    //女宝人数。
//    private final static String BOTTOM_GENDER_SQL2  = "SELECT COUNT(*) FROM pt_user_info a WHERE a.tenant_id='klsfybjy01zfta001' AND a.user_name NOT LIKE '测%' AND a.id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1') AND a.gender='F'";
    private final static String BOTTOM_GENDER_SQL2  = "SELECT COUNT(*) FROM pt_user_info a WHERE a.tenant_id='klsfybjyzfta001' AND a.user_name NOT LIKE '测%' AND a.id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1') AND a.gender='F'";

    //医生意见：同意入园。
    private final static String BOTTOM_DOCTOR_ADV1  = "SELECT COUNT(DISTINCT(a.user_id)) FROM pt_contentdata_info a WHERE a.question_id='sf_ysyj' AND a.question_answer LIKE '%同意入园%' AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1')";
    //医生意见：暂缓入园。
    private final static String BOTTOM_DOCTOR_ADV2  = "SELECT COUNT(DISTINCT(a.user_id)) FROM pt_contentdata_info a WHERE a.question_id='sf_ysyj' AND a.question_answer LIKE '%暂缓入园%' AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1')";

    //有龋齿人数。
    private final static String BOTTOM_TOOTH    = "SELECT COUNT(DISTINCT(a.user_id)) FROM pt_contentdata_info a WHERE a.question_id='sf_kqjc_qcs' AND a.question_answer!='' AND CONVERT(a.question_answer,unsigned)>0 AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1')";
    private final static String BOTTOM_TOTAL_PAT= "SELECT COUNT(DISTINCT(a.user_id)) FROM pt_contentdata_info a WHERE a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1')";

    //血红蛋白。
    //重度贫血
    private final static String BOTTOM_BLOOD1   = "SELECT COUNT(DISTINCT(a.user_id)) FROM pt_contentdata_info a WHERE a.question_id='sf_xcgpj' AND a.question_answer LIKE '%重度贫血%' AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1')";
    //中度贫血
    private final static String BOTTOM_BLOOD2   = "SELECT COUNT(DISTINCT(a.user_id)) FROM pt_contentdata_info a WHERE a.question_id='sf_xcgpj' AND a.question_answer LIKE '%中度贫血%' AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1')";
    //轻度贫血
    private final static String BOTTOM_BLOOD3   = "SELECT COUNT(DISTINCT(a.user_id)) FROM pt_contentdata_info a WHERE a.question_id='sf_xcgpj' AND a.question_answer LIKE '%轻度贫血%' AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1')";

    //生长发育。
    private final static String BOTTOM_GROWTH1  = "SELECT COUNT(DISTINCT(a.user_id)) FROM pt_contentdata_info a WHERE a.question_id='sf_fypj' AND a.question_answer LIKE '%肥胖%' AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1')";
    private final static String BOTTOM_GROWTH2  = "SELECT COUNT(DISTINCT(a.user_id)) FROM pt_contentdata_info a WHERE a.question_id='sf_fypj' AND a.question_answer LIKE '%超重%' AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1')";
    private final static String BOTTOM_GROWTH3  = "SELECT COUNT(DISTINCT(a.user_id)) FROM pt_contentdata_info a WHERE a.question_id='sf_fypj' AND a.question_answer LIKE '%消瘦%' AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1')";
    private final static String BOTTOM_GROWTH4  = "SELECT COUNT(DISTINCT(a.user_id)) FROM pt_contentdata_info a WHERE a.question_id='sf_fypj' AND a.question_answer LIKE '%发育迟缓%' AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1')";
    private final static String BOTTOM_GROWTH5  = "SELECT COUNT(DISTINCT(a.user_id)) FROM pt_contentdata_info a WHERE a.question_id='sf_fypj' AND a.question_answer LIKE '%低体重%' AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1')";

    //今日体检人数（不包含建档人数）。
    private final static String QUERY_TODAY_SQL = "SELECT COUNT(*) FROM pt_task_info WHERE user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1') AND user_id IN (SELECT DISTINCT(id) FROM pt_user_info WHERE user_name NOT LIKE '测%') AND seq_no!=0 AND finish_date=CURDATE()";
    //今日医生体检人数。
    private final static String DOCTOR_TODAY_COUNT  =
            "SELECT b.doctor_name, COUNT(*) AS exam_count FROM pt_task_info a LEFT JOIN dept_doctor_info b ON b.id=a.update_author "+
            "WHERE a.seq_no!=0 AND a.finish_date=CURDATE() AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1') AND a.user_id IN (SELECT DISTINCT(id) FROM pt_user_info WHERE user_name NOT LIKE '测%') GROUP BY b.doctor_name";
    //医生体检总次数排行榜。
    private final static String QUERY_DOCTOR_COUNT  = "SELECT a.create_author AS exam_doctor, b.doctor_name, COUNT(*) AS exam_count FROM pt_user_info a LEFT JOIN dept_doctor_info b ON b.id=a.create_author " +
            "WHERE a.user_name NOT LIKE '测%' AND a.id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1') AND b.doctor_name IS NOT NULL GROUP BY a.create_author ORDER BY exam_count DESC";

    //每月体检人数变化。
    private final static String QUERY_EXAM_COUNT    = "SELECT DATE_FORMAT(create_time, '%Y-%m') AS mont, COUNT(*) AS coun FROM pt_user_info WHERE user_name NOT LIKE '测%' AND id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1') GROUP BY DATE_FORMAT(create_time, '%Y-%m')";


    @Override
    public Map right(final Map<String,Object> paraMap) {
        Map<String, Object> rightMap= new HashMap<>();

        List<Map<String,Object>> resultList = jdbc.queryForList(QUERY_EXAM_COUNT);
        if (resultList.size() > 6)   {
            int delete    = resultList.size() - 6;
            do {
                resultList.remove(0);
                delete--;
            } while (delete != 0);
        }

        List<String> xaxisList  = new ArrayList<>();
        List<Long> seriesList= new ArrayList<>();
        for(Map<String,Object> map : resultList)   {
            xaxisList.add(map.get("mont").toString());
            seriesList.add((Long) map.get("coun"));
        }

        Map tjrsMap = new HashMap();
        tjrsMap.put("xAxis", xaxisList);    //X轴。
        tjrsMap.put("series", seriesList);  //Y轴。

        rightMap.put("近半年内体检人数变化", tjrsMap);
        return rightMap;
    }

    @Override
    public Map left(final Map<String,Object> paraMap) {
        Map<String,Object> leftMap = new HashMap<>();
        Map<String,Object> todayMap = new HashMap<>();
        Integer count   = jdbc.queryForObject(QUERY_TODAY_SQL, Integer.class);
        todayMap.put("今日体检人数", count);
        leftMap.put("今日体检人数", todayMap);

        Map<String,Long> doctorMap   = new HashMap();
        List<Map<String,Object>> resultList = jdbc.queryForList(DOCTOR_TODAY_COUNT);
        for(Map<String,Object> countMap : resultList)   {
            if (null != countMap.get("doctor_name") && !countMap.get("doctor_name").toString().isEmpty()) {
                doctorMap.put(countMap.get("doctor_name").toString(), (Long) countMap.get("exam_count"));
            }
        }
        leftMap.put("医生体检人数", doctorMap);

        return leftMap;
    }

    @Override
    public Map center(final Map<String,Object> paraMap)    {
        Map<String,Integer> centerMap  = new HashMap<>();
        //Integer count   = jdbc.queryForObject(CENTER_PATIENT_SQL, Integer.class);
        //centerMap.put("所有队列患者总数", count);

        Integer count1   = jdbc.queryForObject(CENTER_PATIENT_SQL1, Integer.class);
        centerMap.put("儿童体检", count1);

        Integer count2   = jdbc.queryForObject(CENTER_PATIENT_SQL2, Integer.class);
        centerMap.put("成人体检", count2);

        centerMap.put("所有队列患者总数", count1+count2);

        return centerMap;
    }

    @Override
    public Map bottom(final Map<String,Object> paraMap) {
        Map<String,Object> bottomMap  = new HashMap<>();
        Map<String,Integer> genderMap   = new HashMap<>();
        Integer boy = jdbc.queryForObject(BOTTOM_GENDER_SQL1, Integer.class);
        Integer girl= jdbc.queryForObject(BOTTOM_GENDER_SQL2, Integer.class);
        genderMap.put("男", boy);
        genderMap.put("女", girl);
        List<Map> genderList    = new ArrayList<>();
        Map<String,Object> maleMap = new HashMap<>();
        maleMap.put("name", "男");
        maleMap.put("value", boy);
        genderList.add(maleMap);
        Map<String,Object> femaleMap = new HashMap<>();
        femaleMap.put("name", "女");
        femaleMap.put("value", girl);
        genderList.add(femaleMap);
        bottomMap.put("男女比例", genderList);

        Map<String,Integer> adviceMap   = new HashMap<>();
        Integer agree = jdbc.queryForObject(BOTTOM_DOCTOR_ADV1, Integer.class);
        Integer refuse= jdbc.queryForObject(BOTTOM_DOCTOR_ADV2, Integer.class);
        adviceMap.put("同意入园", agree);
        adviceMap.put("暂缓入园", refuse);
        bottomMap.put("医生意见", adviceMap);

        Map<String,Integer> toothMap   = new HashMap<>();
        final Integer toothYes  = jdbc.queryForObject(BOTTOM_TOOTH, Integer.class);
        final Integer totalPat  = jdbc.queryForObject(BOTTOM_TOTAL_PAT, Integer.class);
        final Integer toothNo   = totalPat - toothYes;
        toothMap.put("有龋齿", toothYes);
        toothMap.put("无龋齿", toothNo);
        bottomMap.put("龋齿人数", toothMap);

        Map<String,Integer> bloodMap    = new HashMap<>();
        final Integer blood1    = jdbc.queryForObject(BOTTOM_BLOOD1, Integer.class);
        final Integer blood2    = jdbc.queryForObject(BOTTOM_BLOOD2, Integer.class);
        final Integer blood3    = jdbc.queryForObject(BOTTOM_BLOOD3, Integer.class);
        final Integer blood4    = totalPat - blood1 -blood2 -blood3;
        bloodMap.put("重度贫血", blood1);
        bloodMap.put("中度贫血", blood2);
        bloodMap.put("轻度贫血", blood3);
        bloodMap.put("正常", blood4);
        bottomMap.put("血红蛋白", bloodMap);

        Map<String,Integer> growthMap    = new HashMap<>();
        final Integer growth1   = jdbc.queryForObject(BOTTOM_GROWTH1, Integer.class);
        final Integer growth2   = jdbc.queryForObject(BOTTOM_GROWTH2, Integer.class);
        final Integer growth3   = jdbc.queryForObject(BOTTOM_GROWTH3, Integer.class);
        final Integer growth4   = jdbc.queryForObject(BOTTOM_GROWTH4, Integer.class);
        final Integer growth5   = jdbc.queryForObject(BOTTOM_GROWTH5, Integer.class);
        final Integer growth6   = totalPat - growth1 - growth2 - growth3 - growth4 - growth5;
        growthMap.put("肥胖", growth1);
        growthMap.put("超重", growth2);
        growthMap.put("消瘦", growth3);
        growthMap.put("发育迟缓",   growth4);
        growthMap.put("低体重",    growth5);
        growthMap.put("正常", growth6);
        bottomMap.put("生长发育", growthMap);

        return bottomMap;
    }
}
